{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "be8b4981",
   "metadata": {},
   "source": [
    "# 数据准备-liepin-PM"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf22335a",
   "metadata": {},
   "source": [
    "## 请求页面准备\n",
    "> 1. 找到页面的数据API接口\n",
    "> 2. 提供正确的用户请求酬载（payload）\n",
    "> 3. 准备请求的headers，增加cookie信息（用户登录之后的cookie），保证数据的合理性\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31e3d2cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install pyecharts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c70850c9",
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'pyecharts'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_6440\\3027988680.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[1;32mimport\u001b[0m \u001b[0mpyecharts\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'pyecharts'"
     ]
    }
   ],
   "source": [
    "import pyecharts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7df093b6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "用户输入职位 = input(\"请输入你要查询的职位：\")\n",
    "用户输入的地区 = input(\"请输入你要查询的地区：\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72589688",
   "metadata": {},
   "outputs": [],
   "source": [
    "地区编码字典 = {\n",
    "    '广州':'050020',\n",
    "    '深圳':'050090'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6f6861b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "\n",
    "url = \"https://apic.liepin.com/api/com.liepin.searchfront4c.pc-search-job\"\n",
    "payload = {\n",
    "    \"data\": {\n",
    "        \"mainSearchPcConditionForm\": {\n",
    "            \"city\": 地区编码字典[用户输入的地区],\n",
    "            \"dq\": 地区编码字典[用户输入的地区],\n",
    "            \"pubTime\": \"\",\n",
    "            \"currentPage\": 0,\n",
    "            \"pageSize\": 40,\n",
    "            \"key\": 用户输入职位,\n",
    "            \"suggestTag\": \"\",\n",
    "            \"workYearCode\": \"0\",\n",
    "            \"compId\": \"\",\n",
    "            \"compName\": \"\",\n",
    "            \"compTag\": \"\",\n",
    "            \"industry\": \"\",\n",
    "            \"salary\": \"\",\n",
    "            \"jobKind\": \"\",\n",
    "            \"compScale\": \"\",\n",
    "            \"compKind\": \"\",\n",
    "            \"compStage\": \"\",\n",
    "            \"eduLevel\": \"\"\n",
    "        },\n",
    "        \"passThroughForm\": {\n",
    "            \"scene\": \"input\",\n",
    "            \"skId\": \"\",\n",
    "            \"fkId\": \"\",\n",
    "            \"ckId\": \"h2c8pxojavrmo1w785z7ueih2ybfpux8\",\n",
    "            \"suggest\": None\n",
    "        }\n",
    "    }\n",
    "}\n",
    "\n",
    "# set the headers\n",
    "headers = {\n",
    "    'Accept': 'application/json, text/plain, */*',\n",
    "    'Accept-Encoding': 'gzip, deflate, br',\n",
    "    'Accept-Language': 'zh-CN,zh;q=0.9',\n",
    "    'Cache-Control': 'no-cache',\n",
    "    'Connection': 'keep-alive',\n",
    "    'Content-Length': '412',\n",
    "    'Content-Type': 'application/json;charset=UTF-8;',\n",
    "    'Cookie':'inited_user=daf7251f92024e8969feb28b0e9ad34c; __gc_id=0baa2ddaa7774d8fba2b9c2c3d8ba166; __uuid=1670205465393.76; XSRF-TOKEN=ENzGaqkHSwuu77YYn7m0IQ; _ga=GA1.1.1852247760.1685687743; __tlog=1685687751918.65%7C00000000%7C00000000%7C00000000%7C00000000; Hm_lvt_a2647413544f5a04f00da7eee0d5e200=1685687755; acw_tc=276077d516856877547452401ee7e4c0793a7cc8414b4954d6510b1d7f452a; UniqueKey=95507c72a8d5ae141a667e00ad0d9493; liepin_login_valid=0; lt_auth=uOZeOXQGxlzxtXfR3zQN4vociI39UWvIpX8EhE0Ahoe%2BCqG04PngSwOGq7EExAMhmhImcMULN7j7M%2BD2wXJD7UcWwGqnl4CyvOW92GECSuBcN8W2vezHl8zRQpQcl0AC8nFbtkIL%2BQ%3D%3D; access_system=C; user_roles=0; user_photo=5f8fa3a679c7cc70efbf444e08u.png; user_name=%E8%AE%B8%E6%99%BA%E8%B6%85; need_bind_tel=false; new_user=false; c_flag=fa43f4d55f3df63a96a7b4f194e214d4; inited_user=daf7251f92024e8969feb28b0e9ad34c; Hm_lpvt_a2647413544f5a04f00da7eee0d5e200=1685688821; imId=c5f9b89f8466dffe6882ca1e5431db9c; imId_0=c5f9b89f8466dffe6882ca1e5431db9c; imClientId=c5f9b89f8466dffeb1921abcfab3aed0; imClientId_0=c5f9b89f8466dffeb1921abcfab3aed0; imApp_0=1; __session_seq=7; __uv_seq=7; fe_im_socketSequence_new_0=1_1_1; fe_im_opened_pages=; fe_im_connectJson_0=%7B%220_95507c72a8d5ae141a667e00ad0d9493%22%3A%7B%22socketConnect%22%3A%222%22%2C%22connectDomain%22%3A%22liepin.com%22%7D%7D; _ga_54YTJKWN86=GS1.1.1685687743.1.1.1685688839.0.0.0',\n",
    "    'Host': 'apic.liepin.com',\n",
    "    'Origin': 'https://www.liepin.com',\n",
    "    'Pragma': 'no-cache',\n",
    "    'Referer': 'https://www.liepin.com/',\n",
    "    'sec-ch-ua': '\"Google Chrome\";v=\"111\", \"Not(A:Brand\";v=\"8\", \"Chromium\";v=\"111\"',\n",
    "    'sec-ch-ua-mobile': '?0',\n",
    "    'sec-ch-ua-platform': '\"Windows\"',\n",
    "    'Sec-Fetch-Dest': 'empty',\n",
    "    'Sec-Fetch-Mode': 'cors',\n",
    "    'Sec-Fetch-Site': 'same-site',\n",
    "    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36',\n",
    "    'X-Client-Type': 'web',\n",
    "    'X-Fscp-Bi-Stat': '{\"location\": \"https://www.liepin.com/zhaopin/?inputFrom=www_index&workYearCode=0&key=%E4%BA%A7%E5%93%81%E7%BB%8F%E7%90%86&scene=input&ckId=htihov8m2frxgy6ywo2wsg2gncnydzlb&dq=\"}',\n",
    "    'X-Fscp-Fe-Version': '',\n",
    "    'X-Fscp-Std-Info': '{\"client_id\": \"40108\"}',\n",
    "    'X-Fscp-Trace-Id': 'e21cd0d2-44d4-4aba-b8bf-9c44f5c5f453',\n",
    "    'X-Fscp-Version': '1.1',\n",
    "    'X-Requested-With': 'XMLHttpRequest',\n",
    "    'X-XSRF-TOKEN': 'ENzGaqkHSwuu77YYn7m0IQ'\n",
    "}\n",
    "\n",
    "# send a POST request with headers\n",
    "r = requests.post(url, data=json.dumps(payload), headers=headers)\n",
    "\n",
    "# extract the JSON data from the response\n",
    "response_data = r.json()\n",
    "\n",
    "# example: print the number of job postings returned\n",
    "print(response_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9ce3169",
   "metadata": {},
   "outputs": [],
   "source": [
    "page = response_data['data']['pagination']['totalPage']\n",
    "page"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00d5fcb6",
   "metadata": {},
   "source": [
    "## 翻页获取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43043d51",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "response_df = []\n",
    "for i in range(page): # 需要判断页面的数据有多少页\n",
    "    payload['data']['mainSearchPcConditionForm']['currentPage']=i\n",
    "    # send a POST request with headers\n",
    "    r = requests.post(url, data=json.dumps(payload), headers=headers)\n",
    "\n",
    "    # extract the JSON data from the response\n",
    "    response_data = r.json()\n",
    "    print(response_data)\n",
    "    df = pd.json_normalize(response_data['data']['data']['jobCardList'])\n",
    "    response_df.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4422f4cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "response_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abf39634",
   "metadata": {},
   "source": [
    "## 数据整理成为表格\n",
    "> 1. pandas 中的concat方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "481b39e9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.concat(response_df)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2ac6009",
   "metadata": {},
   "outputs": [],
   "source": [
    "key = payload['data']['mainSearchPcConditionForm']['key']\n",
    "key"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70fc6de3",
   "metadata": {},
   "source": [
    "## 数据存储"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e032016",
   "metadata": {},
   "outputs": [],
   "source": [
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0affdb43",
   "metadata": {},
   "outputs": [],
   "source": [
    "time.localtime()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9badfd3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "output_time = str(time.localtime().tm_mon)\\\n",
    "             +str(time.localtime().tm_mday)+'_'\\\n",
    "             +str(time.localtime().tm_hour) \\\n",
    "             +str(time.localtime().tm_min)\n",
    "output_time "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d2de632",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按照职位名称和时间导出文件\n",
    "df.to_excel( key +'_liepin_'+output_time+'.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6511a95",
   "metadata": {},
   "source": [
    "# 数据分析\n",
    "\n",
    "> 1. Pandas/Numpy\n",
    "> 2. Pyecharts(bokeh、matplotlab、seaborn、echarts、Tebleau)/更考虑用户的体验"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae96f73c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af2682c2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_excel(key+'_liepin_'+output_time+'.xlsx')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e14c166d",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bf806de",
   "metadata": {},
   "source": [
    "## 筛选存在数据分析价值的列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "048c64d5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_PM_gz =  df[['job.labels','job.refreshTime','job.title','job.salary','job.dq','job.topJob','job.requireWorkYears','job.requireEduLevel','comp.compStage','comp.compName','comp.compIndustry','comp.compScale']]\n",
    "df_PM_gz"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66931fa5",
   "metadata": {},
   "source": [
    "## 广州的PM地区分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40af9bd5",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df_PM_gz['job.dq'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "078e85cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "[ i.split('-')[1] for i in df_PM_gz['job.dq'].value_counts().index.tolist() if '-'  in i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e2e2626",
   "metadata": {},
   "outputs": [],
   "source": [
    "广州地区 = [ i.split('-')[1] for i in df_PM_gz['job.dq'].value_counts().index.tolist() if '-'  in i]\n",
    "广州地区"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c2a3f0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "广州_岗位个数 = df_PM_gz['job.dq'].value_counts().values.tolist()[1:]\n",
    "广州_岗位个数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ed2af33",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip uninstall pyecharts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eaeb2805",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from pyecharts.charts.bar import Bar\n",
    "from pyecharts.charts import options as opts\n",
    "import os\n",
    " \n",
    "bar = Bar()\n",
    "bar.add_xaxis([\"衬衫\", \"毛衣\", \"领带\", \"裤子\", \"风衣\", \"高跟鞋\", \"袜子\"])\n",
    "bar.add_yaxis(\"商家A\", [114, 55, 27, 101, 125, 27, 105])\n",
    "bar.add_yaxis(\"商家B\", [57, 134, 137, 129, 145, 60, 49])\n",
    "bar.set_global_opts(title_opts=opts.TitleOpts(title=\"某商场销售情况\"))\n",
    "bar.render()\n",
    " \n",
    "os.system(\"render.html\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bcb9591a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d6a1a77",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化：以可视化工具数据形态符合的数据进行输入\n",
    "\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Map\n",
    "from pyecharts.faker import Faker\n",
    "\n",
    "c = (\n",
    "    Map()\n",
    "    .add(用户输入的地区, [list(z) for z in zip(广州地区, 广州_岗位个数)], 用户输入的地区)\n",
    "    .set_global_opts(\n",
    "        title_opts=opts.TitleOpts(title='Map-'+用户输入的地区+'地图'), visualmap_opts=opts.VisualMapOpts()\n",
    "    )\n",
    "    .render( key+\"_dq_map_地区分布_\"+output_time+\".html\")\n",
    ")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2eeb9e36",
   "metadata": {},
   "source": [
    "## 职位分布\n",
    "\n",
    "* 知识点：dataframe字符串处理\n",
    "> 1. [pandas.series.str](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.str.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b865f4c9",
   "metadata": {},
   "outputs": [],
   "source": [
    " df_PM_gz['job.title']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7bdd1791",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 还要合并回去原来的行\n",
    "df_PM_gz['job.title'][   df_PM_gz['job.title'].str.contains('（')   ].str.split('（').apply(lambda x:x[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67ce6925",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# 处理过一些，清洗后的数据\n",
    "df_job_title = df_PM_gz['job.title'].apply(lambda x:x.split('（')[0].split('/')[0].split('(')[0]).value_counts()\n",
    "df_job_title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "002d568f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_job_title.index.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02180275",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df_job_title.index.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8c8f438",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_job_title.values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24635f28",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 未处理字符串的数据（不太整洁和干净的数据）\n",
    "df_PM_gz['job.title'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a02f2bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 列表推导式\n",
    "PM_title_words = [(  df_job_title.index.tolist()[i]   ,   df_job_title.values.tolist()[i]  )    for i in range(1,len(df_job_title.index.tolist())) ]\n",
    "PM_title_words"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "601ce6a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import WordCloud\n",
    "from pyecharts.globals import SymbolType\n",
    "\n",
    "c = (\n",
    "    WordCloud()\n",
    "    .add(\"\", PM_title_words, word_size_range=[20, 100], shape=SymbolType.DIAMOND)\n",
    "    .set_global_opts(title_opts=opts.TitleOpts(title=\"WordCloud-shape-diamond\"))\n",
    "    .render( key +\"_wordcloud_map_岗位名称_\"+ output_time+\".html\")\n",
    ")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6dd926d7",
   "metadata": {},
   "source": [
    "## job.labels\n",
    "\n",
    "* 目标：统计labels的数量并做词云图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "692c0136",
   "metadata": {},
   "outputs": [],
   "source": [
    " df_PM_gz['job.labels']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58da6bb3",
   "metadata": {},
   "outputs": [],
   "source": [
    " df_PM_gz['job.labels'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d607c892",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_PM_gz['job.labels'].apply(lambda x:eval(x)).tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cdd091f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 列表的推导式\n",
    "PM_labels_list = [j     for i in df_PM_gz['job.labels'].apply(lambda x:eval(x)).tolist()       for j in i    ]\n",
    "PM_labels_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41a5b2f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建words\n",
    "\n",
    "PM_labels_words = [ (i,PM_labels_list.count(i)) for i in set(PM_labels_list)]\n",
    "PM_labels_words"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "251470c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化词云图\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import WordCloud\n",
    "from pyecharts.globals import SymbolType\n",
    "\n",
    "c = (\n",
    "    WordCloud()\n",
    "    .add(\"\", PM_labels_words, word_size_range=[20, 100], shape=SymbolType.DIAMOND)\n",
    "    .set_global_opts(title_opts=opts.TitleOpts(title=\"WordCloud-shape-diamond\"))\n",
    "    .render( key +\"_wordcloud_map_职位标签_\"+ output_time+\".html\")\n",
    ")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4a2acfd",
   "metadata": {},
   "source": [
    "## 薪资-（平均薪资）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16561a4f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# columns 重命名\n",
    "df_PM_gz = df_PM_gz.rename(columns={\n",
    "    'job.labels':'职位标签',\n",
    "    'job.refreshTime':'职位更新时间',\n",
    "    'job.title':'职位',\n",
    "    'job.salary':'薪资',\n",
    "    'job.dq':'地区',\n",
    "    'job.topJob':'是否top职位',\n",
    "    'job.requireWorkYears':'工作年限',\n",
    "    'job.requireEduLevel':'学历',\n",
    "    'comp.compStage':'公司融资情况',\n",
    "    'comp.compName':'公司名称',\n",
    "    'comp.compIndustry':'行业',\n",
    "    'comp.compScale':'规模'\n",
    "})\n",
    "df_PM_gz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00bdb902",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "非薪资面议 = df_PM_gz [ ~df_PM_gz['薪资'].str.contains(\"面议|元/天\")]\n",
    "非薪资面议"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18d53ef0",
   "metadata": {},
   "outputs": [],
   "source": [
    "非薪资面议_detail = 非薪资面议['薪资'].apply(lambda x:x.split('薪')[0].split('·')).tolist()\n",
    "非薪资面议_detail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6004d3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "(10+15)/2*13/12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6296b488",
   "metadata": {},
   "outputs": [],
   "source": [
    "平均薪资 = [ (int(i[0].split('-')[0]) +int(i[0].split('-')[1].split('k')[0]))/2    \\\n",
    " if len(i)==1 else round((int(i[0].split('-')[0]) +int(i[0].split('-')[1].split('k')[0]))/2*int(i[1])/12,1)     \\\n",
    " for i in 非薪资面议_detail        ] \n",
    "平均薪资"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7ee36c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(平均薪资)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bf7c61d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "非薪资面议['平均薪资']=平均薪资"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7de0c864",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "非薪资面议"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a723ae3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 分地区平均薪资\n",
    "分地区_平均薪资 = 非薪资面议.groupby('地区').agg({'平均薪资':'median'})\n",
    "分地区_平均薪资"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f260e86e",
   "metadata": {},
   "outputs": [],
   "source": [
    "分地区_平均薪资_values =  [round(i[0],1) for i in 分地区_平均薪资.values.tolist()]\n",
    "分地区_平均薪资_values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23b5bf70",
   "metadata": {},
   "outputs": [],
   "source": [
    "分地区_平均薪资_index = 分地区_平均薪资.index.tolist()\n",
    "分地区_平均薪资_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7144cf60",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Bar\n",
    "from pyecharts.faker import Faker\n",
    "\n",
    "\n",
    "c = (\n",
    "    Bar()\n",
    "    .add_xaxis([i.split('-')[1] for i in 分地区_平均薪资_index[1:]])\n",
    "    .add_yaxis(\"地区\",分地区_平均薪资_values[1:])\n",
    "    .set_global_opts(\n",
    "        title_opts=opts.TitleOpts(title=\"PM-分地区-中位数薪资\"),\n",
    "        brush_opts=opts.BrushOpts(),\n",
    "    )\n",
    "    .render( key + \"_bar_with_brush_地区薪资中位数_\"+output_time+'.html')\n",
    ")\n",
    "# c.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eea5da69",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_year_salary = 非薪资面议.groupby('工作年限').agg({'平均薪资':'mean'})\n",
    "df_year_salary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2163b27f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 分工作时间和学历平均薪资\n",
    "df_year_edulevel =  非薪资面议.groupby(['工作年限','学历']).agg({'平均薪资':'mean'})\n",
    "df_year_edulevel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e3d7c57",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分行业\n",
    "df_industry = 非薪资面议.groupby('行业').agg({'平均薪资':'mean'})\n",
    "df_industry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b22612f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(key+'_'+output_time+'_.xlsx') as writer:  \n",
    "    df_year_salary.to_excel(writer, sheet_name='分工作年限平均薪资')\n",
    "    df_year_edulevel.to_excel(writer, sheet_name='分学历平均薪资')\n",
    "    df_industry.to_excel(writer, sheet_name='分行业平均薪资')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9326b535",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b4f207e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70f92c9d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "981e1308",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.16"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "618px",
    "left": "29px",
    "top": "111.125px",
    "width": "327.188px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
